package sqlx

import (
	"fmt"
	"github.com/jmoiron/sqlx"
)

func initDB() (db *sqlx.DB, err error) {
	dsn := "user:password@tcp(127.0.0.1:3306)/sql_test?charset=utf8mb4&parseTime=True"
	// 也可以使用MustConnect连接不成功就panic
	db, err = sqlx.Connect("mysql", dsn)
	if err != nil {
		fmt.Printf("connect DB failed, err:%v\n", err)
		return nil, err
	}
	db.SetMaxOpenConns(20)
	db.SetMaxIdleConns(10)

	return db, nil
}

// 查询单条数据示例
func queryRowDemo(db *sqlx.DB) {
	sqlStr := "select id, name, age from user where id=?"
	var u User
	err := db.Get(&u, sqlStr, 1)
	if err != nil {
		fmt.Printf("get failed, err:%v\n", err)
		return
	}
	fmt.Printf("id:%d name:%s age:%d\n", u.ID, u.Name, u.Age)
}

// 查询多条数据示例
func queryMultiRowDemo(db *sqlx.DB) {
	sqlStr := "select id, name, age from user where id > ?"
	var users []User
	err := db.Select(&users, sqlStr, 0)
	if err != nil {
		fmt.Printf("query failed, err:%v\n", err)
		return
	}
	fmt.Printf("users:%#v\n", users)
}

// 查询多条数据示例
func queryx(db *sqlx.DB) {
	sqlStr := "select id, name, age from user where id > ?"
	var users []*User
	rows, err := db.Queryx(sqlStr, 0)
	if err != nil {
		fmt.Printf("query failed, err:%v\n", err)
		return
	}
	defer rows.Close()

	for rows.Next() {
		var u = &User{}
		err := rows.StructScan(u)
		if err != nil {
			fmt.Printf("Scan error: %v\n", err)
		}
		users = append(users, u)
	}

}
